﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;
namespace PM.Services
{
    class CDSTransactionServices
    {
        private static CDSTransaction GetCDSTransactionFromDataReader(IDataReader dataReader)
        {
            CDSTransaction retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                int intTemp = 0;
                retVal = new CDSTransaction();

                intTemp = 0;
                if (int.TryParse(dataReader["CDS_TRAN_ID"].ToString(), out intTemp))
                    retVal.CDSTranId = intTemp;

                retVal.TransactionDate = dataReader["TRANSACTION_DATE"].ToString();
                retVal.Reference = dataReader["REFERENCE"].ToString();
                retVal.CompanyId = dataReader["COMPANY_ID"].ToString();
                retVal.MainType = dataReader["MAIN_TYPE"].ToString();
   
                intTemp = 0;
                if (int.TryParse(dataReader["SUB_TYPE"].ToString(), out intTemp))
                    retVal.SubType = intTemp;
                intTemp = 0;
                if (int.TryParse(dataReader["CLIENT_PREFIX"].ToString(), out intTemp))
                    retVal.ClientPrefix = intTemp;

                retVal.ClientSuffix = dataReader["CLIENT_SUFFIX"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["JOINT_AC_NO"].ToString(), out intTemp))
                    retVal.JointAcNo = intTemp;
                intTemp = 0;
                if (int.TryParse(dataReader["TRANSACTION_TYPE"].ToString(), out intTemp))
                    retVal.TransactionType = intTemp;

                retVal.TransactionDesc = dataReader["TRANSACTION_DESC"].ToString();
                
                intTemp = 0;
                if (int.TryParse(dataReader["QUANTITY"].ToString(), out intTemp))
                    retVal.Quantity = intTemp;

                intTemp = 0;
                if (int.TryParse(dataReader["TRADE_DATE"].ToString(), out intTemp))
                    retVal.TradeDate = intTemp;
              
            }
            return retVal;
        }


        public static CDSTransaction GetCDSTransactionById(int cdsTransId)
        {
            CDSTransaction retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM CDS_TRAN WHERE CDS_TRAN_ID = " + cdsTransId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetCDSTransactionFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static CDSTransactionCollection GetCDSTransactionCollection()
        {
            CDSTransactionCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM CDS_TRAN";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new CDSTransactionCollection();
                    while (dataReader.Read())
                    {
                        CDSTransaction item = GetCDSTransactionFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static DataTable GetCDSTransactionCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM CDS_TRAN";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static int GetNextCDSTransactionId()
        {
            int retVal = 1;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT MAX(CDS_TRAN_ID) FROM CDS_TRAN";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null && !dataReader.IsClosed && dataReader.Read())
                    {
                        int intTemp = 0;
                        if (int.TryParse(dataReader["MAX(CDS_TRAN_ID)"].ToString(), out intTemp))
                            retVal = intTemp + 1;
                    }
                    else
                    {
                        retVal = 1;
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static int GetNextCDSTransactionId(OracleConnection con, OracleTransaction tran)
        {
            int retVal = 1;
            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT MAX(CDS_TRAN_ID) FROM CDS_TRAN";
                cmd.Connection = con;
                cmd.Transaction = tran;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null && !dataReader.IsClosed && dataReader.Read())
                    {
                        int intTemp = 0;
                        if (int.TryParse(dataReader["MAX(CDS_TRAN_ID)"].ToString(), out intTemp))
                            retVal = intTemp + 1;
                    }
                    else
                    {
                        retVal = 1;
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
            return retVal;

        }


        public static void Add(CDSTransaction cdsTrans)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO CDS_TRAN" +
                                    "(CDS_TRAN_ID, TRANSACTION_DATE, REFERENCE, COMPANY_ID, MAIN_TYPE, SUB_TYPE, CLIENT_PREFIX, CLIENT_SUFFIX, JOINT_AC_NO, TRANSACTION_TYPE, TRANSACTION_DESC, QUANTITY, TRADE_DATE  )" +
                                    "VALUES(:v1,        :v2,            :v3,        :v4,    :v5,        :v6,        :v7,            :v8,        :v9,        :v10,               :v11,           :v12,     :v13)";
                cmd.Parameters.Add(":v1", OracleType.Number).Value = cdsTrans.CDSTranId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = cdsTrans.TransactionDate;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = cdsTrans.Reference;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = cdsTrans.CompanyId;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = cdsTrans.MainType;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = cdsTrans.SubType;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = cdsTrans.ClientPrefix;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = cdsTrans.ClientSuffix;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = cdsTrans.JointAcNo;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = cdsTrans.TransactionType;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = cdsTrans.TransactionDesc;
                cmd.Parameters.Add(":v12", OracleType.Number).Value = cdsTrans.Quantity;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = cdsTrans.TradeDate;
                
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Add(CDSTransaction cdsTrans, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();
            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO CDS_TRAN" +
                                    "(CDS_TRAN_ID, TRANSACTION_DATE, REFERENCE, COMPANY_ID, MAIN_TYPE, SUB_TYPE, CLIENT_PREFIX, CLIENT_SUFFIX, JOINT_AC_NO, TRANSACTION_TYPE, TRANSACTION_DESC, QUANTITY, TRADE_DATE  )" +
                                    "VALUES(:v1,        :v2,            :v3,        :v4,    :v5,        :v6,        :v7,            :v8,        :v9,        :v10,               :v11,           :v12,     :v13)";
                cmd.Parameters.Add(":v1", OracleType.Number).Value = cdsTrans.CDSTranId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = cdsTrans.TransactionDate;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = cdsTrans.Reference;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = cdsTrans.CompanyId;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = cdsTrans.MainType;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = cdsTrans.SubType;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = cdsTrans.ClientPrefix;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = cdsTrans.ClientSuffix;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = cdsTrans.JointAcNo;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = cdsTrans.TransactionType;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = cdsTrans.TransactionDesc;
                cmd.Parameters.Add(":v12", OracleType.Number).Value = cdsTrans.Quantity;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = cdsTrans.TradeDate;

                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }


        public static void Delete(int cdsTransId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM CDS_TRAN WHERE CDS_TRAN_ID = " + cdsTransId ;
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(CDSTransaction cdsTrans)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "UPDATE CDS_TRAN SET " +
                                  "TRANSACTION_DATE = :v2, " +
                                  "REFERENCE = :v3, " +
                                  "COMPANY_ID = :v4, " +
                                  "MAIN_TYPE = :v5, " +
                                  "SUB_TYPE = :v6, " +
                                  "CLIENT_PREFIX = :v7, " +
                                  "CLIENT_SUFFIX = :v8, " +
                                  "JOINT_AC_NO = :v9, " +
                                  "TRANSACTION_TYPE = :v10, " +
                                  "TRANSACTION_DESC = :v11, " +
                                  "QUANTITY = :v12, " +
                                  "TRADE_DATE = :v13 " +                                   
                                  "WHERE CDS_TRAN_ID = :v1";


                cmd.Parameters.Add(":v1", OracleType.Number).Value = cdsTrans.CDSTranId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = cdsTrans.TransactionDate;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = cdsTrans.Reference;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = cdsTrans.CompanyId;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = cdsTrans.MainType;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = cdsTrans.SubType;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = cdsTrans.ClientPrefix;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = cdsTrans.ClientSuffix;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = cdsTrans.JointAcNo;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = cdsTrans.TransactionType;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = cdsTrans.TransactionDesc;
                cmd.Parameters.Add(":v12", OracleType.Number).Value = cdsTrans.Quantity;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = cdsTrans.TradeDate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
